﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
//using System.IO;
using MCSFramework.Model.TEST;
using MCSFramework.BLL.TEST;
//using System.Text;
public partial class GridView转Excel : System.Web.UI.Page
{
    protected void Page_Load(object sender, EventArgs e)
    {
        if (!IsPostBack)
        {
            bind();
        }
    }
    public void bind()
    {
        IList<TEST_Client> ClientList = TEST_ClientBLL.GetModelList("1=1");
        gvRecord.AllowPaging = false;
        gvRecord.DataSource = ClientList;
        gvRecord.DataBind();
        GroupRectangle(gvRecord, 0, 0, 2, 2);
        GroupRectangle(gvRecord, 0, 3, 3, 3);
        GroupRectangle(gvRecord, 2, 4, 4, 5);
        GroupRectangle(gvRecord, 5, 0, 5, 3);
        //GroupRow(gvRecord, 0, 0, 3);
        //GroupCol(gvRecord, 3, 2, 5);
    }

    #region gridview事件
    protected void gvRecord_RowCreated(object sender, GridViewRowEventArgs e)
    {
        switch (e.Row.RowType)
        {
            case DataControlRowType.Header:
                //总表头
                TableCellCollection tcHeader = e.Row.Cells;
                tcHeader.Clear();
                //第一行表头
                tcHeader.Add(new TableHeaderCell());
                tcHeader[0].Attributes.Add("bgcolor", "DarkSeaBlue");
                tcHeader[0].Attributes.Add("colspan", "6"); //合并第一行的6列
                tcHeader[0].Text = "用户基本信息</th></tr><tr>";
                //第二行表头
                tcHeader.Add(new TableHeaderCell());
                tcHeader[1].Attributes.Add("bgcolor", "DarkSeaGreen");
                tcHeader[1].Attributes.Add("colspan", "3");
                tcHeader[1].Text = "基本信息";
                tcHeader.Add(new TableHeaderCell());
                tcHeader[2].Attributes.Add("bgcolor", "DarkSeaGreen");
                tcHeader[2].Attributes.Add("colspan", "3");
                tcHeader[2].Text = "籍贯信息</th></tr><tr>";
                //第三行表头
                tcHeader.Add(new TableHeaderCell());
                tcHeader[3].Attributes.Add("bgcolor", "Khaki");
                tcHeader[3].Text = "ID";
                tcHeader.Add(new TableHeaderCell());
                tcHeader[4].Attributes.Add("bgcolor", "Khaki");
                tcHeader[4].Text = "姓名";
                tcHeader.Add(new TableHeaderCell());
                tcHeader[5].Attributes.Add("bgcolor", "Khaki");
                tcHeader[5].Text = "号码";
                tcHeader.Add(new TableHeaderCell());
                tcHeader[6].Attributes.Add("bgcolor", "Khaki");
                tcHeader[6].Text = "住址";
                tcHeader.Add(new TableHeaderCell());
                tcHeader[7].Attributes.Add("bgcolor", "Khaki");
                tcHeader[7].Text = "邮编";
                tcHeader.Add(new TableHeaderCell());
                tcHeader[8].Attributes.Add("bgcolor", "Khaki");
                tcHeader[8].Text = "城市";
                break;
        }
    }
    protected void gvRecord_PageIndexChanged(object sender, EventArgs e)
    {
    }
    protected void gvRecord_PageIndexChanging(object sender, GridViewPageEventArgs e)
    {
        this.gvRecord.PageIndex = e.NewPageIndex;
        this.gvRecord.BottomPagerRow.Visible = false;
        bind();
    }
    protected void gvRecord_PreRender(object sender, EventArgs e)
    {
    }
    public override void VerifyRenderingInServerForm(Control control)
    {
        //override VerifyRenderingInServerForm.
    }
    #endregion

    protected void btnExcel_Click(object sender, EventArgs e)
    {
        NPOIToExcel.OptimizaToExcel(gvRecord, Server.MapPath("test.xls"));
        /*
        gvRecord.AllowPaging = false;
        bind();
        GroupRow(gvRecord, 0);
        DateTime dt = System.DateTime.Now;
        string str = dt.ToString("yyyyMMddhhmmss");
        str = str + ".xls";
        //GridViewToExcel(gvRecord, "application/ms-excel", str);
        DumpExcel(gvRecord, str);
        gvRecord.AllowPaging = true;
        bind();//AllowPaging 修改后要重新绑定数据源
        GroupRow(gvRecord, 0);
         */
    }

    private void DumpExcel(GridView gv, string FileName)
    {
        //带格式导出
        string style = @"<style> .text { mso-number-format:\@; } </style>";
        Response.ClearContent();
        Response.Charset = "GB2312";
        Response.ContentEncoding = System.Text.Encoding.UTF8;
        Response.AppendHeader("content-disposition", "attachment; filename=" + HttpUtility.UrlEncode(FileName, System.Text.Encoding.UTF8).ToString());
        Response.ContentType = "application/excel";
        System.IO.StringWriter sw = new System.IO.StringWriter();
        HtmlTextWriter htw = new HtmlTextWriter(sw);
        gv.RenderControl(htw);
        // Style is added dynamically
        Response.Write(style);
        Response.Write(sw.ToString());
        Response.End();
    }
    //重载VerifyRenderingInServerForm方法，否则运行的时候会出现如下错误提示：“类型“GridView”的控件“GridView1”必须放在具有 runat=server 的窗体标记内”

    #region 合并成为矩形单元格
    /// <summary>
    /// 合并成为矩形单元格
    /// </summary>
    /// <param name="GridView1"></param>
    /// <param name="sRow">开始行</param>
    /// <param name="sCol">开始列</param>
    /// <param name="eRow">结束行</param>
    /// <param name="eCol">结束列</param>
    public static void GroupRectangle(GridView GridView1, int sRow, int sCol, int eRow, int eCol)
    {
        if (sRow < 0 || sRow > eRow || eRow > GridView1.Rows.Count - 1)
        {
            return;
        }
        if (sCol < 0 || sCol > eCol || eCol > GridView1.Columns.Count - 1)
        {
            return;
        }
        TableCell oldTc = GridView1.Rows[sRow].Cells[sCol];
        oldTc.ColumnSpan = eCol - sCol + 1;
        oldTc.RowSpan = eRow - sRow + 1;
        //oldTc.ColumnSpan = eCol - sCol > 0 ? eCol - sCol : 1;
        //oldTc.RowSpan = eRow - sRow > 0 ? eRow - sRow : 1;
        for (int i = sRow; i <= eRow; i++)
        {
            for (int j = sCol; j <= eCol; j++)
            {
                if (i == sRow && j == sCol)//起始单元格
                {
                    //oldTc.ColumnSpan = 1;
                    //oldTc.RowSpan = 1;
                    continue;
                }                
                
                TableCell tc = GridView1.Rows[i].Cells[j];
                tc.Visible = false;
            }
        }        
        oldTc.Visible = true;
        oldTc.VerticalAlign = VerticalAlign.Middle;
        oldTc.HorizontalAlign = HorizontalAlign.Center;
    }
    #endregion

    #region 合并单元格 合并某一行的所有列
    ///　 <summary>　
    ///　 合并GridView中某行相同信息的行（单元格）
    ///　 </summary>　
    ///　 <param　 name="GridView1">GridView对象</param>　
    ///　 <param　 name="cellNum">需要合并的行</param>
    public static void GroupRow(GridView GridView1, int rows)
    {
        TableCell oldTc = GridView1.Rows[rows].Cells[0];
        for (int i = 1; i < GridView1.Rows[rows].Cells.Count; i++)
        {
            TableCell tc = GridView1.Rows[rows].Cells[i];　 //Cells[0]就是你要合并的列
            if (oldTc.Text == tc.Text)
            {
                tc.Visible = false;
                if (oldTc.ColumnSpan == 0)
                {
                    oldTc.ColumnSpan = 1;
                }
                oldTc.ColumnSpan++;
                oldTc.VerticalAlign = VerticalAlign.Middle;
            }
            else
            {
                oldTc = tc;
            }
        }
    }
    #endregion
    #region 合并单元格 合并一行中的几列
    /// <summary>
    /// 合并单元格 合并一行中的几列
    /// </summary>
    /// <param name="GridView1">GridView ID</param>
    /// <param name="rows">行</param>
    /// <param name="sCol">开始列</param>
    /// <param name="eCol">结束列</param>
    public static void GroupRow(GridView GridView1, int rows, int sCol, int eCol)
    {
        TableCell oldTc = GridView1.Rows[rows].Cells[sCol];
        for (int i = 1; i < eCol - sCol; i++)
        {
            TableCell tc = GridView1.Rows[rows].Cells[i + sCol];　 //Cells[0]就是你要合并的列
            tc.Visible = false;
            if (oldTc.ColumnSpan == 0)
            {
                oldTc.ColumnSpan = 1;
            }
            oldTc.ColumnSpan++;
            oldTc.VerticalAlign = VerticalAlign.Middle;
        }
    }
    #endregion
    #region 合并单元格 合并某一列所有行
    /// <summary>
    /// 合并GridView中某列相同信息的行（单元格）
    /// </summary>
    /// <param name="GridView1"></param>
    /// <param name="cellNum"></param>
    public static void GroupCol(GridView GridView1, int cols)
    {
        if (GridView1.Rows.Count < 1 || cols > GridView1.Rows[0].Cells.Count - 1)
        {
            return;
        }
        TableCell oldTc = GridView1.Rows[0].Cells[cols];
        for (int i = 1; i < GridView1.Rows.Count; i++)
        {
            TableCell tc = GridView1.Rows[i].Cells[cols];
            if (oldTc.Text == tc.Text)
            {
                tc.Visible = false;
                if (oldTc.RowSpan == 0)
                {
                    oldTc.RowSpan = 1;
                }
                oldTc.RowSpan++;
                oldTc.VerticalAlign = VerticalAlign.Middle;
            }
            else
            {
                oldTc = tc;
            }
        }
    }
    #endregion
    #region 合并单元格 合并某一列中的某些行
    /// <summary>
    /// 合并单元格 合并某一列中的某些行
    /// </summary>
    /// <param name="GridView1">GridView ID</param>
    /// <param name="cellNum">列</param>
    /// <param name="sRow">开始行</param>
    /// <param name="eRow">结束列</param>
    public static void GroupCol(GridView GridView1, int cols, int sRow, int eRow)
    {
        if (GridView1.Rows.Count < 1 || cols > GridView1.Columns.Count - 1)
        {
            return;
        }
        TableCell oldTc = GridView1.Rows[sRow].Cells[cols];
        for (int i = 1; i < eRow - sRow; i++)
        {
            TableCell tc = GridView1.Rows[sRow + i].Cells[cols];
            tc.Visible = false;
            if (oldTc.RowSpan == 0)
            {
                oldTc.RowSpan = 1;
            }
            oldTc.RowSpan++;
            oldTc.VerticalAlign = VerticalAlign.Middle;
        }
    }
    #endregion
}